using System;
using System.Collections.Generic;
using System.Text;
using DTO;
using System.Data;
using System.Data.SqlClient;

namespace DAO
{
    /// <summary> 
    ///Author: nnamthach@gmail.com 
    /// <summary>
    public class TestDataAccess : Connection
    {
        #region Local Variable
        private string _strSPInsertName = "dbo.[procTest_add]";
        private string _strSPUpdateName = "dbo.[procTest_update]";
        private string _strSPDeleteName = "dbo.[procTest_delete]";
        private string _strSPGetName = "dbo.[procTest_get]";
        private string _strSPGetAllName = "dbo.[procTest_getall]";
        private string _strSPGetPages = "dbo.[procTest_getpaged]";
        private string _strSPIsExist = "dbo.[procTest_isexist]";
        private string _strTableName = "[Test]";
        private string _strSPGetTransferOutName = "dbo.[procTest_gettransferout]";
        string _strSPGetByT0Name = "procTest_getT0";
        string _strSPGetByT0UsrName = "procTest_getT0Urs";
        string _strSPGetByUsrName = "procTest_getUrs";
        string _strSPGetByNoName = "procTest_getNo";
        string _strSPGetByCateName = "procTest_getUrsCate";
        string _strSPGetByTypeName = "procTest_gettype";
        #endregion Local Variable

        #region Method
        public TestInfo Get(
        Int64 ID,
        ref string sErr)
        {
            TestInfo objEntr = new TestInfo();
            connect();
            InitSPCommand(_strSPGetName);
            AddParameter(TestInfo.Field.ID.ToString(), ID);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (TestInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        protected override object GetDataFromDataRow(DataTable dt, int i)
        {
            TestInfo result = new TestInfo();
            result.ID = (dt.Rows[i][TestInfo.Field.ID.ToString()] == DBNull.Value ? 0 : Convert.ToInt64(dt.Rows[i][TestInfo.Field.ID.ToString()]));
            result.Name = (dt.Rows[i][TestInfo.Field.Name.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.Name.ToString()]));
            result.Description = (dt.Rows[i][TestInfo.Field.Description.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.Description.ToString()]));
            result.Begin_Time = (dt.Rows[i][TestInfo.Field.Begin_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][TestInfo.Field.Begin_Time.ToString()]));
            result.End_Time = (dt.Rows[i][TestInfo.Field.End_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][TestInfo.Field.End_Time.ToString()]));
            result.Duration_Time = (dt.Rows[i][TestInfo.Field.Duration_Time.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][TestInfo.Field.Duration_Time.ToString()]));
            result.Ip_range = (dt.Rows[i][TestInfo.Field.Ip_range.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.Ip_range.ToString()]));
            result.Results_To_User = (dt.Rows[i][TestInfo.Field.Results_To_User.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Results_To_User.ToString()]));
            result.Score_Right = (dt.Rows[i][TestInfo.Field.Score_Right.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][TestInfo.Field.Score_Right.ToString()]));
            result.Score_Wrong = (dt.Rows[i][TestInfo.Field.Score_Wrong.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][TestInfo.Field.Score_Wrong.ToString()]));
            result.Max_Score = (dt.Rows[i][TestInfo.Field.Max_Score.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][TestInfo.Field.Max_Score.ToString()]));
            result.Pass_Score = (dt.Rows[i][TestInfo.Field.Pass_Score.ToString()] == DBNull.Value ? 0 : Convert.ToDecimal(dt.Rows[i][TestInfo.Field.Pass_Score.ToString()]));
            result.Random_Question_S = (dt.Rows[i][TestInfo.Field.Random_Question_S.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Random_Question_S.ToString()]));
            result.Random_Answers_S = (dt.Rows[i][TestInfo.Field.Random_Answers_S.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Random_Answers_S.ToString()]));
            result.Random_Question_O = (dt.Rows[i][TestInfo.Field.Random_Question_O.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Random_Question_O.ToString()]));
            result.Random_Answers_O = (dt.Rows[i][TestInfo.Field.Random_Answers_O.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Random_Answers_O.ToString()]));
            result.Logout_On_Timeout = (dt.Rows[i][TestInfo.Field.Logout_On_Timeout.ToString()] == DBNull.Value ? 0 : Convert.ToInt32(dt.Rows[i][TestInfo.Field.Logout_On_Timeout.ToString()]));
            result.Type = (dt.Rows[i][TestInfo.Field.Type.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.Type.ToString()]));
            result.Enabled = (dt.Rows[i][TestInfo.Field.Enabled.ToString()] == DBNull.Value ? true : Convert.ToBoolean(dt.Rows[i][TestInfo.Field.Enabled.ToString()]));
            result.ANAL_T9 = (dt.Rows[i][TestInfo.Field.ANAL_T9.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T9.ToString()]));
            result.ANAL_T8 = (dt.Rows[i][TestInfo.Field.ANAL_T8.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T8.ToString()]));
            result.ANAL_T7 = (dt.Rows[i][TestInfo.Field.ANAL_T7.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T7.ToString()]));
            result.ANAL_T6 = (dt.Rows[i][TestInfo.Field.ANAL_T6.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T6.ToString()]));
            result.ANAL_T5 = (dt.Rows[i][TestInfo.Field.ANAL_T5.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T5.ToString()]));
            result.ANAL_T4 = (dt.Rows[i][TestInfo.Field.ANAL_T4.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T4.ToString()]));
            result.ANAL_T3 = (dt.Rows[i][TestInfo.Field.ANAL_T3.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T3.ToString()]));
            result.ANAL_T2 = (dt.Rows[i][TestInfo.Field.ANAL_T2.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T2.ToString()]));
            result.ANAL_T1 = (dt.Rows[i][TestInfo.Field.ANAL_T1.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T1.ToString()]));
            result.ANAL_T0 = (dt.Rows[i][TestInfo.Field.ANAL_T0.ToString()] == DBNull.Value ? "" : Convert.ToString(dt.Rows[i][TestInfo.Field.ANAL_T0.ToString()]));

            return result;
        }

        public DataTable GetAll(
        ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetAllName);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        /// <summary>
        /// Return 1: Table is exist Identity Field
        /// Return 0: Table is not exist Identity Field
        /// Return -1: Erro
        /// </summary>
        /// <param name="tableName"></param>
        public Int32 Add(TestInfo objEntr, ref string sErr)
        {
            int ret = -1;
            connect();
            InitSPCommand(_strSPInsertName);
            AddParameter(TestInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(TestInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TestInfo.Field.Begin_Time.ToString(), objEntr.Begin_Time);
            AddParameter(TestInfo.Field.End_Time.ToString(), objEntr.End_Time);
            AddParameter(TestInfo.Field.Duration_Time.ToString(), objEntr.Duration_Time);
            AddParameter(TestInfo.Field.Ip_range.ToString(), objEntr.Ip_range);
            AddParameter(TestInfo.Field.Results_To_User.ToString(), objEntr.Results_To_User);
            AddParameter(TestInfo.Field.Score_Right.ToString(), objEntr.Score_Right);
            AddParameter(TestInfo.Field.Score_Wrong.ToString(), objEntr.Score_Wrong);
            AddParameter(TestInfo.Field.Max_Score.ToString(), objEntr.Max_Score);
            AddParameter(TestInfo.Field.Pass_Score.ToString(), objEntr.Pass_Score);
            AddParameter(TestInfo.Field.Random_Question_S.ToString(), objEntr.Random_Question_S);
            AddParameter(TestInfo.Field.Random_Answers_S.ToString(), objEntr.Random_Answers_S);
            AddParameter(TestInfo.Field.Random_Question_O.ToString(), objEntr.Random_Question_O);
            AddParameter(TestInfo.Field.Random_Answers_O.ToString(), objEntr.Random_Answers_O);
            AddParameter(TestInfo.Field.Logout_On_Timeout.ToString(), objEntr.Logout_On_Timeout);
            AddParameter(TestInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(TestInfo.Field.Enabled.ToString(), objEntr.Enabled);
            AddParameter(TestInfo.Field.ANAL_T9.ToString(), objEntr.ANAL_T9);
            AddParameter(TestInfo.Field.ANAL_T8.ToString(), objEntr.ANAL_T8);
            AddParameter(TestInfo.Field.ANAL_T7.ToString(), objEntr.ANAL_T7);
            AddParameter(TestInfo.Field.ANAL_T6.ToString(), objEntr.ANAL_T6);
            AddParameter(TestInfo.Field.ANAL_T5.ToString(), objEntr.ANAL_T5);
            AddParameter(TestInfo.Field.ANAL_T4.ToString(), objEntr.ANAL_T4);
            AddParameter(TestInfo.Field.ANAL_T3.ToString(), objEntr.ANAL_T3);
            AddParameter(TestInfo.Field.ANAL_T2.ToString(), objEntr.ANAL_T2);
            AddParameter(TestInfo.Field.ANAL_T1.ToString(), objEntr.ANAL_T1);
            AddParameter(TestInfo.Field.ANAL_T0.ToString(), objEntr.ANAL_T0);

            try
            {
                //command.ExecuteNonQuery();
                object tmp = executeSPScalar();
                if (tmp != null && tmp != DBNull.Value)
                    ret = Convert.ToInt32(tmp);
                else
                    ret = 0;
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);

            return ret;
        }

        public string Update(TestInfo objEntr)
        {
            connect();
            InitSPCommand(_strSPUpdateName);
            AddParameter(TestInfo.Field.ID.ToString(), objEntr.ID);
            AddParameter(TestInfo.Field.Name.ToString(), objEntr.Name);
            AddParameter(TestInfo.Field.Description.ToString(), objEntr.Description);
            AddParameter(TestInfo.Field.Begin_Time.ToString(), objEntr.Begin_Time);
            AddParameter(TestInfo.Field.End_Time.ToString(), objEntr.End_Time);
            AddParameter(TestInfo.Field.Duration_Time.ToString(), objEntr.Duration_Time);
            AddParameter(TestInfo.Field.Ip_range.ToString(), objEntr.Ip_range);
            AddParameter(TestInfo.Field.Results_To_User.ToString(), objEntr.Results_To_User);
            AddParameter(TestInfo.Field.Score_Right.ToString(), objEntr.Score_Right);
            AddParameter(TestInfo.Field.Score_Wrong.ToString(), objEntr.Score_Wrong);
            AddParameter(TestInfo.Field.Max_Score.ToString(), objEntr.Max_Score);
            AddParameter(TestInfo.Field.Pass_Score.ToString(), objEntr.Pass_Score);
            AddParameter(TestInfo.Field.Random_Question_S.ToString(), objEntr.Random_Question_S);
            AddParameter(TestInfo.Field.Random_Answers_S.ToString(), objEntr.Random_Answers_S);
            AddParameter(TestInfo.Field.Random_Question_O.ToString(), objEntr.Random_Question_O);
            AddParameter(TestInfo.Field.Random_Answers_O.ToString(), objEntr.Random_Answers_O);
            AddParameter(TestInfo.Field.Logout_On_Timeout.ToString(), objEntr.Logout_On_Timeout);
            AddParameter(TestInfo.Field.Type.ToString(), objEntr.Type);
            AddParameter(TestInfo.Field.Enabled.ToString(), objEntr.Enabled);
            AddParameter(TestInfo.Field.ANAL_T9.ToString(), objEntr.ANAL_T9);
            AddParameter(TestInfo.Field.ANAL_T8.ToString(), objEntr.ANAL_T8);
            AddParameter(TestInfo.Field.ANAL_T7.ToString(), objEntr.ANAL_T7);
            AddParameter(TestInfo.Field.ANAL_T6.ToString(), objEntr.ANAL_T6);
            AddParameter(TestInfo.Field.ANAL_T5.ToString(), objEntr.ANAL_T5);
            AddParameter(TestInfo.Field.ANAL_T4.ToString(), objEntr.ANAL_T4);
            AddParameter(TestInfo.Field.ANAL_T3.ToString(), objEntr.ANAL_T3);
            AddParameter(TestInfo.Field.ANAL_T2.ToString(), objEntr.ANAL_T2);
            AddParameter(TestInfo.Field.ANAL_T1.ToString(), objEntr.ANAL_T1);
            AddParameter(TestInfo.Field.ANAL_T0.ToString(), objEntr.ANAL_T0);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public string Delete(
        Int64 ID
        )
        {
            connect();
            InitSPCommand(_strSPDeleteName);
            AddParameter(TestInfo.Field.ID.ToString(), ID);

            string sErr = "";
            try
            {
                excuteSPNonQuery();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return sErr;
        }

        public DataTableCollection Get_Page(TestInfo obj, string orderBy, int pageIndex, int pageSize, ref String sErr)
        {
            string whereClause = CreateWhereClause(obj);
            DataTableCollection dtList = null;
            connect();
            InitSPCommand(_strSPGetPages);

            AddParameter("WhereClause", whereClause);
            AddParameter("OrderBy", orderBy);
            AddParameter("PageIndex", pageIndex);
            AddParameter("PageSize", pageSize);

            try
            {
                dtList = executeCollectSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            return dtList;
        }

        public Boolean IsExist(
        Int64 ID
        )
        {
            connect();
            InitSPCommand(_strSPIsExist);
            AddParameter(TestInfo.Field.ID.ToString(), ID);

            string sErr = "";
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            if (sErr != "") ErrorLog.SetLog(sErr);
            if (list.Rows.Count == 1)
                return true;
            return false;
        }

        private string CreateWhereClause(TestInfo obj)
        {
            String result = "";

            return result;
        }

        public DataTable Search(string columnName, string columnValue, string condition, string tableName, ref string sErr)
        {
            string query = "select * from " + _strTableName + " where " + columnName + " " + condition + " " + columnValue;
            DataTable list = new DataTable();
            connect();
            try
            {
                list = executeSelectQuery(query);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            //    if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        public DataTable GetTransferOut(string dtb, object from, object to, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetTransferOutName);
            AddParameter("DB", dtb);
            AddParameter("FROM", from);
            AddParameter("TO", to);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
        #endregion Method

        public DataTable GetAllByT0(string analcode, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByT0Name);
            AddParameter("T0", analcode);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetAllByT0_User(string analcode, long id, string type, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByT0UsrName);
            AddParameter("T0", analcode);
            AddParameter("IDUser", id);
            AddParameter("Type", type);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public DataTable GetTmpAll(long idUsr, string type, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByUsrName);
            AddParameter("IDUser", idUsr);
            AddParameter("Type", type);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }

        public TestInfo GetByNo(int no, ref string sErr)
        {
            TestInfo objEntr = new TestInfo();
            connect();
            InitSPCommand(_strSPGetByNoName);
            AddParameter("No", no);

            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP(command);
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();

            if (list.Rows.Count > 0)
                objEntr = (TestInfo)GetDataFromDataRow(list, 0);
            //if (dr != null) list = CBO.FillCollection(dr, ref list);
            if (sErr != "") ErrorLog.SetLog(sErr);
            return objEntr;
        }

        public DataTable GetTmpAll(long idUser, string cate, string topic, string level, string type, ref string sErr)
        {
            connect();
            InitSPCommand(_strSPGetByCateName);
            AddParameter("IDUser", idUser);
            AddParameter("Type", type);
            AddParameter("Cate", cate);
            AddParameter("Topic", topic);
            AddParameter("Level", level);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    
        public DataTable GetByType(string type, ref string sErr)
        {
            // TODO: Implement this method
            connect();
            InitSPCommand(_strSPGetByTypeName);
            AddParameter("Type", type);
            DataTable list = new DataTable();
            try
            {
                list = executeSelectSP();
            }
            catch (Exception ex)
            {
                sErr = ex.Message;
            }
            disconnect();


            if (sErr != "") ErrorLog.SetLog(sErr);
            return list;
        }
    }
}
